IF OBJECT_ID('ssf.dpnd_insert_object_header') IS NOT NULL
	DROP PROCEDURE ssf.dpnd_insert_object_header
GO

CREATE PROCEDURE ssf.dpnd_insert_object_header
	@object_header XML (ssf.object_header)
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @sql VARCHAR(8000) -- or (MAX)
			, @nsql NVARCHAR(4000) -- or (MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@header_object_id INT
			, @header_object_name NVARCHAR(256)
-- ============

-- ============
/*<code_comment><![CDATA[exit if no object header]]></code_comment>*/
IF @object_header IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[make sure header object name includes schema]]></code_comment>*/
SET @header_object_name = @object_header.value('(/object_header/object_summary/dpnd_object_name)[1]', 'NVARCHAR(256)')
IF PARSENAME(@header_object_name, 2) IS NULL
	SET @header_object_name = OBJECT_SCHEMA_NAME(OBJECT_ID(@header_object_name)) + '.' + @header_object_name

IF @header_object_name IS NULL
BEGIN
	RAISERROR('Could not parse header object name.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[begin transaction]]></code_comment>*/
BEGIN TRANSACTION

BEGIN TRY
-- ============

-- ============
/*<code_comment><![CDATA[merge header object data]]></code_comment>*/
;
WITH dpnd_object
AS (
	SELECT *
	FROM ssf.dpnd_header_object
	WHERE dpnd_object_name = @header_object_name
)
MERGE
INTO dpnd_object t
USING (
	SELECT @header_object_name AS dpnd_object_name
		, @object_header.value('(/object_header/object_summary/object_type)[1]', 'NVARCHAR(128)') AS object_type
		, @object_header.value('(/object_header/object_summary/purpose)[1]', 'NVARCHAR(MAX)') AS purpose
		, @object_header.value('(/object_header/object_summary/return_data_type)[1]', 'NVARCHAR(128)') AS return_data_type
		, @object_header.value('(/object_header/object_summary/create_date)[1]', 'SMALLDATETIME') AS create_date
		, @object_header.value('(/object_header/object_summary/create_user)[1]', 'NVARCHAR(128)') AS create_user
) s
ON t.dpnd_object_name = s.dpnd_object_name
WHEN MATCHED
THEN UPDATE SET t.object_type = s.object_type
	, t.purpose = s.purpose
	, t.return_data_type = s.return_data_type
	, t.create_date = s.create_date
	, t.create_user = s.create_user
WHEN NOT MATCHED BY TARGET
THEN INSERT (
	dpnd_object_name
	, object_type
	, purpose
	, return_data_type
	, create_date
	, create_user
)
VALUES (
	s.dpnd_object_name
	, s.object_type
	, s.purpose
	, s.return_data_type
	, s.create_date
	, s.create_user
);

SET @header_object_id = (
	SELECT header_object_id
	FROM ssf.dpnd_header_object
	WHERE dpnd_object_name = @header_object_name
)
-- ============

-- ============
/*<code_comment><![CDATA[merge parameter data]]></code_comment>*/
;
WITH dpnd_parameter
AS (
	SELECT *
	FROM ssf.dpnd_header_parameter
	WHERE header_object_id = @header_object_id
)
MERGE
INTO dpnd_parameter t
USING (
	SELECT @header_object_id AS header_object_id
		, parameter_dependency.value('(/parameter_dependency/parameter_order)[1]', 'SMALLINT') AS parameter_order
		, parameter_dependency.value('(/parameter_dependency/parameter_name)[1]', 'NVARCHAR(128)') AS parameter_name
		, parameter_dependency.value('(/parameter_dependency/parameter_desc)[1]', 'NVARCHAR(2048)') AS parameter_desc
		, parameter_dependency.value('(/parameter_dependency/data_type)[1]', 'NVARCHAR(128)') AS data_type
		, parameter_dependency.value('(/parameter_dependency/default_value)[1]', 'NVARCHAR(512)') AS default_value
		, parameter_dependency.value('(/parameter_dependency/return_type)[1]', 'VARCHAR(16)') AS return_type
	FROM (
		SELECT t.c.query('.') AS parameter_dependency FROM @object_header.nodes('/object_header/parameter_dependency') AS t(c)
	) x
) s
ON t.header_object_id = s.header_object_id
AND t.parameter_order = s.parameter_order
WHEN MATCHED
THEN UPDATE SET t.parameter_name = s.parameter_name
	, t.parameter_desc = s.parameter_desc
	, t.data_type = s.data_type
	, t.default_value = s.default_value
	, t.return_type = s.return_type
WHEN NOT MATCHED BY TARGET
THEN INSERT (
	header_object_id
	, parameter_order
	, parameter_name
	, parameter_desc
	, data_type
	, default_value
	, return_type
)
VALUES (
	s.header_object_id
	, s.parameter_order
	, s.parameter_name
	, s.parameter_desc
	, s.data_type
	, s.default_value
	, s.return_type
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
;
-- ============

-- ============
/*<code_comment><![CDATA[merge dependency data]]></code_comment>*/
;
WITH dpnd_dependency
AS (
	SELECT *
	FROM ssf.dpnd_dependency_object
	WHERE header_object_id = @header_object_id
)
MERGE
INTO dpnd_dependency t
USING (
	SELECT @header_object_id AS header_object_id
		, CAST('INPUT' AS VARCHAR(128)) AS dependency_type
		, input_dependency.value('(/input_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
		, input_dependency.value('(/input_dependency/object_type)[1]', 'NVARCHAR(128)') AS object_type
		, CAST(NULL AS VARCHAR(128)) AS change_type
	FROM (
		SELECT t.c.query('.') AS input_dependency FROM @object_header.nodes('/object_header/input_dependency') AS t(c)
	) x
	UNION ALL
	SELECT @header_object_id
		, CAST('TEMPORARY' AS VARCHAR(128)) AS dependency_type
		, temporary_dependency.value('(/temporary_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
		, temporary_dependency.value('(/temporary_dependency/object_type)[1]', 'NVARCHAR(128)') AS object_type
		, CAST(NULL AS VARCHAR(128)) AS change_type
	FROM (
		SELECT t.c.query('.') AS temporary_dependency FROM @object_header.nodes('/object_header/temporary_dependency') AS t(c)
	) x
	UNION ALL
	SELECT @header_object_id
		, CAST('OUTPUT' AS VARCHAR(128)) AS dependency_type
		, output_dependency.value('(/output_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
		, output_dependency.value('(/output_dependency/object_type)[1]', 'NVARCHAR(128)') AS object_type
		, output_dependency.value('(/output_dependency/change_type)[1]', 'NVARCHAR(128)') AS change_type
	FROM (
		SELECT t.c.query('.') AS output_dependency FROM @object_header.nodes('/object_header/output_dependency') AS t(c)
	) x
)s
ON t.header_object_id = s.header_object_id
AND t.dependency_type = s.dependency_type
AND t.dpnd_object_name = s.dpnd_object_name
WHEN MATCHED
THEN UPDATE SET t.object_type = s.object_type
	, t.change_type = s.change_type
WHEN NOT MATCHED BY TARGET
THEN INSERT (
	header_object_id
	, dependency_type
	, dpnd_object_name
	, object_type
	, change_type
)
VALUES (
	s.header_object_id
	, s.dependency_type
	, s.dpnd_object_name
	, s.object_type
	, s.change_type
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
;
-- ============

-- ============
/*<code_comment><![CDATA[merge comment data]]></code_comment>*/
;
WITH dpnd_comment
AS (
	SELECT *
	FROM ssf.dpnd_object_comment
	WHERE header_object_id = @header_object_id
)
MERGE
INTO dpnd_comment t
USING (
	SELECT @header_object_id AS header_object_id
		, object_comment.value('(/object_comment/comment_sequence)[1]', 'INT') AS comment_sequence
		, object_comment.value('(/object_comment/code_comment)[1]', 'NVARCHAR(MAX)') AS code_comment
	FROM (
		SELECT t.c.query('.') AS object_comment FROM @object_header.nodes('/object_header/object_comment') AS t(c)
	) x
) s
ON t.header_object_id = s.header_object_id
AND t.comment_sequence = s.comment_sequence
WHEN MATCHED
THEN UPDATE SET t.code_comment = s.code_comment
WHEN NOT MATCHED BY TARGET
THEN INSERT (
	header_object_id
	, comment_sequence
	, code_comment
)
VALUES (
	s.header_object_id
	, s.comment_sequence
	, s.code_comment
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
;
-- ============

-- ============
/*<code_comment><![CDATA[end transaction]]></code_comment>*/
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
END CATCH

IF @sql_error <> 0
BEGIN
	ROLLBACK TRANSACTION
	
	RAISERROR('The MERGE of the object header failed:%s%s', 16, 1, @crlf, @error_message)
	RETURN @sql_error
END

COMMIT TRANSACTION
-- ============

RETURN @@ERROR
GO
